# -*- coding = utf-8 -*-
# @Time : 2024/6/4 01:34
# @Author : 0505200213石浪
# @File : utils.py
# @Software : PyCharm
import socketio_events
from db_operations import Database

# app/utils.py
homeData = []
moviesData = []
comments = []
db = Database()


def get_movies():
    global moviesData
    sql = "SELECT * FROM movieFriends.movies;"
    data1 = db.execute(sql)
    moviesData = data1


def getHomeData():
    global homeData
    sql = "SELECT count(*) FROM movieFriends.movies;"
    num = db.execute(sql)[0][0]
    sql = "SELECT AVG(grade) FROM movieFriends.movies;"
    grade = db.execute(sql)[0][0]
    sql = "SELECT sum(evaluatorsNum) FROM movieFriends.movies;"
    commentsNum = db.execute(sql)[0][0] / 10000
    sql = "SELECT COUNT(*) FROM movieFriends.user;"
    userNum = db.execute(sql)[0][0]
    homeData = (num, round(grade, 3), round(commentsNum, 2), userNum)


def get_comments(movie_id):
    global comments
    sql = "SELECT * FROM movieFriends.comments WHERE movie_id = %s"
    params = (str(movie_id),)
    comments = db.execute(sql, params)


def get_second_comments_from_database(comment_id):
    # 二次评论数据
    dict_list = []
    sql = "SELECT * FROM movieFriends.second_comments WHERE user_comment_id = (%s);"
    params = comment_id
    second_comments = db.execute(sql, params)
    # 将查询结果转换为 Python 字典或列表，并返回
    for comment in second_comments:
        dict_list.append({
            'id': comment[0],
            'user_comment_id': comment[1],
            'user_id': comment[2],
            'content': comment[3],
            'username': comment[4],
            'avatar': comment[5],
            'time': comment[6]
        })
    # 假设这里返回的是一个列表，包含了多条二次评论的信息
    return dict_list


def unread_num(username):
    no_read_message = socketio_events.no_read_message
    sql = "SELECT count(*) FROM movieFriends.private_messages WHERE receiver_username=%s"
    num = db.execute(sql, username)[0][0]

    if username in no_read_message:
        num += len(list(no_read_message[username]))
    return num
